package cn.edu.seu.herald.informationplatform.dao.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import cn.edu.seu.herald.informationplatform.bean.league.LeagueUser;
import cn.edu.seu.herald.informationplatform.bean.league.Vote;

public class VoteDataAccess extends DataAccessObject {
	
	private static final String COLUMN_ID = "id";
	
	private static final String COLUMN_LEAGUE_ID = "league_id";
	
	private static final String COLUMN_TITLE = "title";
	
	private static final String COLUMN_AVAILABLE_NUM = "available_num";
	
	private static VoteDataAccess _instance;
	
	private LeagueUserDao _leagueUserDao;
	
	public static VoteDataAccess getInstance() {
		if (_instance == null) {
			_instance = new VoteDataAccess();
		}
		return _instance;
	}
	
	protected VoteDataAccess() {
		_leagueUserDao = LeagueUserDao.getDao();
	}
	
	private static final String STATEMENT_RETRIEVE_VOTE_BY_ID =
			"SELECT * FROM lg_vote WHERE id=?;";
	
	private Vote getVoteByResultSet(ResultSet rs) throws SQLException {
		int voteId = rs.getInt(COLUMN_ID);
		int leagueId = rs.getInt(COLUMN_LEAGUE_ID);
		String title = rs.getString(COLUMN_TITLE);
		int availableNumber = rs.getInt(COLUMN_AVAILABLE_NUM);
		
		LeagueUser l = _leagueUserDao.getLeagueUser(leagueId);
		Vote vote = new Vote();
		vote.setId(voteId);
		vote.setLeague(l);
		vote.setLeagueId(leagueId);
		vote.setTitle(title);
		vote.setAvailableNumber(availableNumber);
		
		return vote;
	}
	
	public Vote getVoteById(int id) throws SQLException {
		Connection connection = getConnectionLeague();
		try {
			PreparedStatement ps =
					connection.prepareStatement(STATEMENT_RETRIEVE_VOTE_BY_ID);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			
			if (!rs.next()) {
				return null;
			}
			
			return getVoteByResultSet(rs);
		} finally {
			connection.close();
		}
	}
	
	private static final String STATEMENT_RETRIEVE_VOTE_BY_LEAGUE_ID =
			"SELECT * FROM lg_vote WHERE league_id=?;";
	
	public Vote getVoteByLeagueId(int leagueId) throws SQLException {
		Connection connection = getConnectionLeague();
		try {
			PreparedStatement ps =
					connection.prepareStatement(STATEMENT_RETRIEVE_VOTE_BY_LEAGUE_ID);
			ps.setInt(1, leagueId);
			ResultSet rs = ps.executeQuery();
			
			if (!rs.next()) {
				return null;
			}
			
			return getVoteByResultSet(rs);
		} finally {
			connection.close();
		}
	}
	
	private static final String STATEMENT_CREATE_VOTE =
			"INSERT INTO lg_vote (league_id, title, available_num) " +
			"VALUES (?, ?, ?);";
	
	public int createNewVote(Vote vote) throws SQLException {
		Connection connection = getConnectionLeague();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_CREATE_VOTE, Statement.RETURN_GENERATED_KEYS);
			ps.setInt(1, vote.getLeagueId());
			ps.setString(2, vote.getTitle());
			ps.setInt(3, vote.getAvailableNumber());
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			
			if (!rs.next()) {
				return -1;
			}
			return rs.getInt(1);
		} finally {
			connection.close();
		}
	}
	
	private static final String STATEMENT_UPDATE_VOTE =
			"UPDATE lg_vote SET league_id=?, title=?, available_num=? WHERE id=?;";
	
	public void updateVote(Vote vote) throws SQLException {
		Connection connection = getConnectionLeague();
		try {
			PreparedStatement ps =
					connection.prepareStatement(STATEMENT_UPDATE_VOTE);
			ps.setInt(1, vote.getLeagueId());
			ps.setString(2, vote.getTitle());
			ps.setInt(3, vote.getAvailableNumber());
			ps.setInt(4, vote.getId());
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	private static final String STATEMENT_UPDATE_VOTE_BY_LEAGUE_ID =
			"UPDATE lg_vote SET title=?, available_num=? WHERE league_id=?;";
	
	public void updateVoteByLeagueId(Vote vote) throws SQLException {
		Connection connection = getConnectionLeague();
		try {
			PreparedStatement ps =
					connection.prepareStatement(STATEMENT_UPDATE_VOTE_BY_LEAGUE_ID);
			ps.setString(1, vote.getTitle());
			ps.setInt(2, vote.getAvailableNumber());
			ps.setInt(3, vote.getLeagueId());
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	private static final String STATEMENT_DELETE_VOTE_BY_ID =
			"DELETE FROM lg_vote WHERE id=?;";
	
	public void deleteVoteById(int id) throws SQLException {
		Connection connection = getConnectionLeague();
		try {
			PreparedStatement ps =
					connection.prepareStatement(STATEMENT_DELETE_VOTE_BY_ID);
			ps.setInt(1, id);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	private static final String STATEMENT_DELETE_VOTE_BY_LEAGUE_ID =
			"DELETE FROM lg_vote WHERE league_id=?;";
	
	public void deleteVoteByLeagueId(int leagueId) throws SQLException {
		Connection connection = getConnectionLeague();
		try {
			PreparedStatement ps =
					connection.prepareStatement(STATEMENT_DELETE_VOTE_BY_LEAGUE_ID);
			ps.setInt(1, leagueId);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}

}
